/**
 * 
 */
package cz.geoserver.KMLpublisher.util;

import java.sql.ResultSet;
import java.sql.SQLException;

import cz.geoserver.KMLpublisher.db.SQLExecutor;
import cz.geoserver.KMLpublisher.db.model.BBOX;
import cz.geoserver.KMLpublisher.db.model.ClusterInfo;

/**
 * Class allows using hierarchy mechanism
 * Loads row of cluster info table from database for current combination of data table and clustered attribute,
 * compute area of current extent in DB as polygon in CRS EPSG:3035,
 * computes usable hierarchy level for current bbox
 *
 * @author mkepka
 *
 */
public class ClusterUtil {

    /**
     * Constructor creates instance of class
     */
    public ClusterUtil(){
    }

    /**
     * Method loads row from database cluster_info table for current combination 
     * of data table name and clustered attribute name
     * @param tableName - name of data table with geometries as String
     * @param attributeName - name of clustered attribute as String
     * @return ClusterInfo object that holds all columns from cluster_info table
     * @throws SQLException - Throws SQLException if was not found any row for current combination 
     */
    public ClusterInfo getClustersInfo(String tableName, String attributeName) throws SQLException{
        String query = "SELECT * FROM clusters_info WHERE data_table='"+tableName+"' AND data_attribute='"+attributeName+"';";
        ResultSet res = SQLExecutor.getInstance().executeQuery(query);
        if(res!=null){
            if(res.next()){
                return new ClusterInfo(res);
            }
            else{
                throw new SQLException("It was not found any cluster info!");
            }
        }
        else{
            throw new SQLException("It was not found any cluster info!");
        }
    }

    /**
     * Method computes area of current BBOX as polygon in CRS EPSG:3035 in database
     * @param bbox - BBOX object with coordinates
     * @return the area of BBOX in square meters or 0.0 if the area cannot be computed
     * @throws SQLException if the area cannot be computed
     */
    public double computeAreaInDB(BBOX bbox) throws SQLException{
        String areaQuery = "SELECT st_Area(st_transform(st_PolygonFromText('"+bbox.getBboxPolygon()+"', 4326), 3035)) as area;";
        ResultSet resArea = SQLExecutor.getInstance().executeQuery(areaQuery);
        if(resArea != null){
            if(resArea.next()){
                return resArea.getDouble("area");
            }
        }
        else{
            throw new SQLException("Area was not calculated by DB!");
        }
        return 0.0;
    }

    /**
     * Method computes levels of hierarchy for current window extent (BBOX)
     * Method supposes that levels go from 0 to n, where 0 are original geometries 
     * and n is maximal level of representatives.
     * Method computes levels as a fraction of maximal BBOX area:
     * used formula: level = round_up[(maximal_level-1)-[log(max_BBOX_area/current_BBOX_area)/2*log(2)])]
     * @param cluster - ClusterInfo object with information about current hierarchy
     * @param bbox - BBOX object with information about current BBOX
     * @return level of hierarchy - the level of hierarchy suitable for current BBOX as int
     * @throws SQLException
     */
    public int computeLevel(ClusterInfo cluster, BBOX bbox) throws SQLException{
        double bboxArea = computeAreaInDB(bbox);
        if(bboxArea == 0.0){
            throw new SQLException("Level cannot be calculated because of zero area!");
        }
        else{
            double level = (cluster.getLevelsCount() - 1)-(Math.log10(cluster.getMaxBBOXArea()/bboxArea)/(2*Math.log10(2)));
            if (level > (cluster.getLevelsCount()-1)){
                return (cluster.getLevelsCount()-1);
            }
            else if(level<0){
                return 0;
            }
            else{
                return (int) Math.ceil(level);
            }
        }
    }

    /**
     * Method calculates scale of window from BBOX and number of horizontal and vertical pixels
     * Method is not used yet!
     * @param horizontalPixs - height of current window in pixels
     * @param verticalPixs - width of current window in pixels
     * @param bbox - BBOX of current window
     */
    protected void calcWindowDimension(int horizontalPixs, int verticalPixs, BBOX bbox){
        double xmin = bbox.getXmin();
        double xmax = bbox.getYmax();
        double ymin = bbox.getYmin();
        double ymax = bbox.getYmax();
        int R = 6378000; //radius of Earth in meters
        /*
         * U, dU, dV - must be in radians!!!
         * 
         * dp = R.dU
         * dr = R.cos(U).dV
         */
        double dMeridian = R*(ymax-ymin); // in meters
        double dSouthParallel = R*Math.cos(ymin)*(xmax-xmin); // in meters
        double dNorthParallel = R*Math.cos(ymax)*(xmax-xmin); // in meters
        
        double meridianScalePM = verticalPixs/dMeridian; // pix/meter
        double sParallelScalePM = horizontalPixs/dSouthParallel; // pix/meter
        double nParallelScalePM = horizontalPixs/dNorthParallel; // pix/meter
        
        double meridianScaleMP = dMeridian/verticalPixs; // meter/pix
        double sParallelScaleMP = dSouthParallel/horizontalPixs; // meter/pix
        double nParallelScaleMP = dNorthParallel/horizontalPixs; // meter/pix
    }
}